import pymysql
import sys

class Stu:
    def __init__(self):
        global  cursor
        global db
        db = pymysql.connect(host="localhost", user="root", password="", db="stuOnline", charset="utf8")
        cursor = db.cursor()
        print("数据已连接")

    def execute(self,sql):
        '''执行sql'''
        cursor.execute(sql)

    def select(self):
        '''查询信息学生信息'''
        sql = "select * from stu"
        m=cursor.execute(sql)
        alist=cursor.fetchall()
        print("{0:<9}{1:<9}{2:<9}{3:10}".format("学员id","姓名","年龄","班级"))
        for vo in alist:
            for i in range(4):
                print("{0:<10}".format(vo[i]),end=" ")
            print()


    def delete(self,username):
        '''根据姓名删除学员，并且将学号连续'''
        sql="delete from stu where username='{}';".format(username)
        # print(sql)
        m = cursor.execute(sql)
        # sql1="alter table stu AUTO_INCREMENT=(select max(id) from stu)"
        # m = cursor.execute(sql1)
        db.commit()

    def insert(self,data):
        '''添加学员信息'''
        sql="insert into stu(username,age,classid) values ('%s',%d,'%s')"%(data)
        # print(sql)
        m = cursor.execute(sql)
        db.commit()

    def update(self,name,age):
        '''更新学员年龄'''
        sql="update stu set age={} where username='{}'".format(age,name)
        # print(sql)
        m = cursor.execute(sql)
        db.commit()

    def close(self):
        db.close()
        print("数据库已关闭")
s=Stu()
while True:
    print("=" * 12, "学员管理系统", "=" * 14)
    print("{0:1} {1:13} {2:15}".format(" ","1. 查看学员信息","2. 添加学员信息"))
    print("{0:1} {1:13} {2:15}".format(" ","3. 删除学员信息","4. 更改学员信息"))
    print("{0:1} {1:13} ".format(" ","5. 退出系统"))
    print("="*40)
    key = input("请输入对应的选择：")
    #根据键盘值，判断并执行对应的操作
    if key == "1":
        print("="*12,"学员信息浏览","="*14)
        s.select()
        input("按回车键继续：")
    elif key == "2":
        print("="*12,"学员信息添加","="*14)
        try :
            username=input("请输入要添加的姓名：")
            age=int(input("请输入要添加的年龄："))
            classid=input("请输入要添加的班级号：")
            data=(username,age,classid)
            s.insert(data)
            s.select()
            input("按回车键继续：")
        except Exception as e:
            print("error:",e)
            print("录入数据有误，添加失败")

    elif key == "3":
        print("="*12,"学员信息删除","="*14)
        s.select()
        try:
            username = input("请输入你要删除的信息姓名：")
            s.delete(username)
            s.select()
            input("按回车键继续：")
        except Exception as e:
            print("error:",e)
    elif key == "4":
        print("="*12,"学员信息更新","="*14)
        s.select()
        try:
            username = input("请输入你要更新信息的学生姓名：")
            age = int(input("请输入你要更新信息的学生年龄："))
            s.update(username,age)
            s.select()
            input("按回车键继续：")
        except Exception as e:
            print("error:",e)
            print("录入数据有误，更新失败")
    elif key == "5":
        print("=" * 12, "再见", "=" * 14)
        break
    else:
        print("======== 无效的键盘输入！ ==========")

s.close()
sys.exit()

